import csv
import sqlite3
import StringIO
from datetime import datetime
from neuro.common import isFloat, isInteger
from neuro.exceptions import IllegalArgumentException

def csvToDatabase(csvfile, table_name, db_conn=None, date_formats={}):
    '''
    Convert CSV to database. First row is assumed to be column headers ::
    
        >>> from neuro.db import csvToDatabase
        >>> reader = open('file.csv', 'rb')
        >>> conn = csvToDatabase(reader, "table1", date_formats={'Date': '%Y-%m-%d'})
        >>> cursor = conn.cursor()
        >>> cursor.execute("SELECT foo FROM table1")
        >>> for row in cursor:
        ...    print row["foo"]
        
    :param csvfile: CSV file name or csv.reader object
    :type csvfile: str, _csv.reader
    :param table_name: Datbase table name
    :type table_name: str
    :param db_conn: Use existing database connection
    :type db_conn: sqlite3.Connection
    :param date_formats: Date formatting
    :type date_formats: dict
    :rtype: sqlite3.Connection
    '''
    if isinstance(csvfile, basestring):
        reader = csv.reader(StringIO.StringIO(csvfile), delimiter=",")
    else:
        reader = csvfile
    
    headers = reader.next()
    if len(headers) == 0:
        return None
        
    ## --- create database connection if needed
    if(not db_conn):
        db_conn = sqlite3.connect(':memory:')
        db_conn.row_factory = sqlite3.Row
    cursor = db_conn.cursor()
        
    ## --- create table
    headers = map(lambda x: x.replace('/', '_'), headers)
    cursor.execute('CREATE TABLE ' + table_name + ' (' + ",".join(headers) + ')')
    
    ## --- build up insert data (we could do individual inserts/commits)
    data = []
    for row in reader:
        row_data = []
        for i in range(len(row)):
            value = row[i].strip()
            header = headers[i]
            # 1. if the value is empty, store is as null
            # 2. if the value is a date, format it according to date_formats
            # 3. if the value is an integer, cast it to an int
            # 4. if the value is a float, case it to a float
            # 5. default, store the value as a string
            if value == "":
                row_data.append(None)
            elif header in date_formats:
                if not isinstance(date_formats[header], basestring):
                    raise IllegalArgumentException("Date field '" + date_formats[header] + "' format must be a string")
                row_data.append(datetime.strptime(value, date_formats[header]))
            elif isInteger(value):
                row_data.append(int(value))
            elif isFloat(value):
                row_data.append(float(value))
            else:
                row_data.append(value)
        data.append(tuple(row_data))
    
    question_marks = ",".join("?" * len(headers))
    cursor.executemany('INSERT INTO ' + table_name + ' VALUES (' + question_marks + ')', data)
    db_conn.commit()
    
    return db_conn